﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace AcessoDados
{
    public class ProdutoDAO : IDisposable
    {
        SqlConnection conexao = null;
        public int Save(Negocio.Produto produto)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                string query = @"INSERT INTO Produto
                                        (ProID,ProDes,ProDesCom,ProVal,TppID)
                                    VALUES
                                        (#ProID, '#ProDes', '#ProDesCom', #ProVal, #TppID)";
                int id = this.getPK();
                command.CommandText = query.Replace("#ProID", Convert.ToString(id))
                                            .Replace("#ProDes", produto.ProDes)
                                            .Replace("#ProDesCom", produto.ProDesCom)
                                            .Replace("#ProVal", Convert.ToString(produto.ProVal))
                                            .Replace("#TppID", Convert.ToString(produto.TppID));
                command.ExecuteNonQuery();
                return id;
            }
        }

        public int getPK()
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT *
	                                         FROM Produto P";
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);
                return dt.Rows.Count + 1;
            }            
        }

        public void Update(Negocio.Produto produto)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                string query = @"UPDATE Produto
                                   SET ProDes = '#ProDes',
                                       ProDesCom = '#ProDesCom',
                                       ProVal = #ProVal,
                                       TppID = #TppID
                                 WHERE ProID = #ProID ";
                command.CommandText = query.Replace("#ProDesCom", produto.ProDesCom)
                                            .Replace("#ProDes", produto.ProDes)                                            
                                            .Replace("#ProVal", Convert.ToString(produto.ProVal))
                                            .Replace("#TppID", Convert.ToString(produto.TppID))
                                            .Replace("#ProID", Convert.ToString(produto.ProID));
                command.ExecuteNonQuery();
            }
        }

        public void Delete(int id)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"";
                command.ExecuteNonQuery();
            }
        }

        public List<Negocio.Produto> Select(int tipPro)
        {
            conexao = new ConnectionFactory().criaConexao();
            List<Negocio.Produto> produtos = new List<Negocio.Produto>();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT P.ProID, P.ProDes, P.ProVal, 'PmgLoc' =
	                                        case 
		                                        when I.PmgLoc is null then '/images/imagenotfound.jpg'
		                                        else I.PmgLoc
	                                        end
	                                         FROM Produto P left JOIN ProImg I
                                        ON P.ProID = I.ProID and I.PmgId = 1
                                        where P.TppID =" + tipPro.ToString();
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    Negocio.Produto produto = new Negocio.Produto();
                    produto.ProID = Convert.ToInt32(row["ProID"]);
                    produto.ProDes = Convert.ToString(row["ProDes"]);
                    produto.ProImgPri = Convert.ToString(row["PmgLoc"]);
                    produtos.Add(produto);
                }

            }

            return produtos;
        }

        public List<Negocio.Produto> SelectRandomico(int tipPro)
        {
            conexao = new ConnectionFactory().criaConexao();
            List<Negocio.Produto> produtos = new List<Negocio.Produto>();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT top 30 P.ProID, P.ProDes, P.ProVal, 'PmgLoc' =
	                                        case 
		                                        when I.PmgLoc is null then '/images/imagenotfound.jpg'
		                                        else I.PmgLoc
	                                        end
	                                         FROM Produto P left JOIN ProImg I
                                        ON P.ProID = I.ProID and I.PmgId = 1
                                        where P.TppID = " + tipPro.ToString() + " or " + tipPro.ToString() + " = 99 " +
                                        "order by NEWID()";
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    Negocio.Produto produto = new Negocio.Produto();
                    produto.ProID = Convert.ToInt32(row["ProID"]);
                    produto.ProDes = Convert.ToString(row["ProDes"]);
                    produto.ProImgPri = Convert.ToString(row["PmgLoc"]);
                    produtos.Add(produto);
                }

            }

            return produtos;
        }

        public List<Negocio.Produto> SelectByID(int proID)
        {
            conexao = new ConnectionFactory().criaConexao();
            List<Negocio.Produto> produtos = new List<Negocio.Produto>();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT P.ProID, P.ProDes, P.ProVal, ProDesCom, 'PmgLoc' =
	                                        case 
		                                        when I.PmgLoc is null then '/images/imagenotfound.jpg'
		                                        else I.PmgLoc
	                                        end, TppID
	                                         FROM Produto P left JOIN ProImg I
                                        ON P.ProID = I.ProID and I.PmgId = 1
                                        where P.ProID =" + proID.ToString();
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    Negocio.Produto produto = new Negocio.Produto();
                    produto.ProID = Convert.ToInt32(row["ProID"]);
                    produto.ProDes = Convert.ToString(row["ProDes"]);
                    produto.ProDesCom = Convert.ToString(row["ProDesCom"]);
                    produto.ProImgPri = Convert.ToString(row["PmgLoc"]);
                    produto.TppID = Convert.ToInt32(row["TppID"]);
                    produtos.Add(produto);
                }

            }

            return produtos;
        }

        public void Dispose()
        {
            if (conexao != null)
            {
                conexao.Close();
                conexao.Dispose();
            }
        }
    }

    public class ProImgDAO : IDisposable
    {
        SqlConnection conexao = null;
        public void Save(Negocio.ProImg proImg)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                string query = @"INSERT INTO ProImg
                                       (ProID,PmgId,PmgNom,PmgLoc)
                                         VALUES
                                               ( #ProID ,
                                                 #PmgId , 
                                                 '#PmgNom' ,
                                                 '#PmgLoc' )";
                command.CommandText = query.Replace("#ProID", Convert.ToString(proImg.ProID))
                                            .Replace("#PmgId", Convert.ToString(getNewPK(proImg.ProID)))
                                            .Replace("#PmgNom", proImg.PgmNom)
                                            .Replace("#PmgLoc", proImg.PgmLoc);
                command.ExecuteNonQuery();
            }
        }

        public int getNewPK(int id)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT ProID FROM PROIMG WHERE PROID = " + id.ToString() + " ORDER BY PMGID";
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);
                return dt.Rows.Count + 1;
             
            }
        }

        public void Update(Negocio.ProImg proImg)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"";
                command.ExecuteNonQuery();
            }
        }

        public void Delete(int proID, int imgID)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"";
                command.ExecuteNonQuery();
            }
        }

        public List<Negocio.ProImg> Select(int id)
        {
            conexao = new ConnectionFactory().criaConexao();
            List<Negocio.ProImg> listaImagens = new List<Negocio.ProImg>();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT PMGID, PMGNOM, PMGLOC FROM PROIMG WHERE PROID = " + id.ToString() + " ORDER BY PMGID";
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
                    Negocio.ProImg imagem = new Negocio.ProImg();
                    imagem.PmgID = Convert.ToInt32(row["PMGID"]);
                    imagem.PgmNom = Convert.ToString(row["PMGNOM"]);
                    imagem.PgmLoc = Convert.ToString(row["PMGLOC"]);
                    listaImagens.Add(imagem);
                }

            }

            return listaImagens;
        }

        public void Dispose()
        {
            if (conexao != null)
            {
                conexao.Close();
                conexao.Dispose();
            }
        }
    }

    public class ProPesDAO : IDisposable
    {
        SqlConnection conexao = null;
        public List<Negocio.ProPes> Select(int id)
        {
            conexao = new ConnectionFactory().criaConexao();
            List<Negocio.ProPes> precos = new List<Negocio.ProPes>();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT PROID, PTMID, PTMPES, PTMVAL FROM PROPES WHERE PROID = " + id.ToString() + " ORDER BY PTMID";
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
                    Negocio.ProPes _preco = new Negocio.ProPes();
                    _preco.PtmID = Convert.ToInt32(row["PTMID"]);
                    _preco.PtmPes = Convert.ToDecimal(row["PTMPES"]);
                    _preco.PtmVal = Convert.ToDecimal(row["PTMVAL"]);
                    precos.Add(_preco);
                }

            }

            return precos;
        }

        public void Save(Negocio.ProPes proPes)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;

                string query = @"INSERT INTO ProPes
                                       (ProID
                                       ,PtmID
                                       ,PtmPes
                                       ,PtmVal)
                                         VALUES
                                               ( #ProID ,
                                                 #PtmID , 
                                                 #PtmPes ,
                                                 #PtmVal )";
                command.CommandText = query.Replace( "#ProID",  Convert.ToString(proPes.ProID))
                                            .Replace("#PtmID", Convert.ToString(this.getNewPK(proPes.ProID)))
                                            .Replace("#PtmPes", Convert.ToString(proPes.PtmPes))
                                            .Replace("#PtmVal", Convert.ToString(proPes.PtmVal).Replace(",","."));
                command.ExecuteNonQuery();
            }
        }

        public int getNewPK(int id)
        {
            conexao = new ConnectionFactory().criaConexao();
            using (conexao)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conexao;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = @"SELECT ProID FROM PROPES WHERE PROID = " + id.ToString() + " ORDER BY PTMID";
                SqlDataAdapter sdAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                sdAdapter.Fill(dt);           
                if (dt.Rows.Count != 0)
                {
                    return dt.Rows.Count + 1;
                }
                else { return 1; }
            }
        }

        public void Dispose()
        {
            if (conexao != null)
            {
                conexao.Close();
                conexao.Dispose();
            }
        }
    }
}
